﻿CREATE proc [dbo].[SaveContact] 
	@ContactID int = null output,
	@IsOrganisation bit,
	@Name1 nvarchar(50),
	@Name2 nvarchar(50) = null,
	@Sex tinyint = null,
	@LegalFormKey int = null,
	@VatRegNo nvarchar(50) = null,
	@Notice nvarchar(max) = null,
	@Birthday datetime2 = null,
	@SearchKeyword nvarchar(50) = null,
	@Department nvarchar(50) = null,
	@UserID int,
	@Title1 int = null,
	@Title2 int = null,
	@Title3 int = null,
	@Addresses dbo.udtAddressList readonly,
	@ContactOpportunities dbo.udtContactsOpportunities readonly,
	@Principals dbo.udtContactsPrincipals readonly
as
begin
    set nocount on
    
    declare @ErrorMessage nvarchar(400) = 'SaveContact: ',
			@SetTran bit = 0,
			@RC int,
			@UserName nvarchar(25),
			@Now datetime2(7) = sysdatetime(),
			@AddressID int = 0,
			@AddressTypeID int,
			@IsActive bit,
			@Street nvarchar(50) = null,
			@StreetAdditional nvarchar(50) = null,
			@ZipCodeID int = null,
			@OpportunityID int = 0,
			@PrincipalID int = 0,
			@Opportunity nvarchar(50) = null, 
			@OpportunityTypeID int = 0;
    
    
    if  @IsOrganisation is null
    begin
        set @ErrorMessage += '@IsOrganisation is null'
        
        goto ExitOnError
    end
    
    if  @Name1 is null
    begin
        set @ErrorMessage += '@Name1 is null'
        
        goto ExitOnError
    end
    
    if  @UserID is null
    begin
        set @ErrorMessage += '@UserID is null'
        
        goto ExitOnError
    end
    
	select	@UserName = coalesce(u.LoginName, 'NA.')
	from	[User].datUser u
	where	u.UserID = @UserID

	if xact_state() = 0
	begin
		set @SetTran = 1
		begin tran
	end
    
    begin try
        
        --=========================			Contact   =============================================================
        ;with contact
        as (
            select  @ContactID 'ContactID',
                    @IsOrganisation 'IsOrganisation',
					@Name1 'Name1',
					@Name2 'Name2',
					case when coalesce(@IsOrganisation,0) = 0 then coalesce(@Sex,1) else 3 end 'Sex',
					@LegalFormKey 'LegalFormKey',
					@VatRegNo 'VatRegNo',
					@Notice 'Notice',
					@Birthday 'Birthday',
					@SearchKeyword 'SearchKeyword',
					@Department 'Department',
					@Now 'RecordDateTime',
					@UserName 'UserName'

        )
        merge into dbo.datContacts t
			using contact s on
				s.ContactID = t.ContactID
		when not matched by target then
		insert (
			IsOrganisation, 
			FirstName, 
			LastName, 
			CompanyName, 
			CompanyNameAdditional, 
			Sex,
			LegalFormKey, 
			VatRegNo, 
			Notice, 
			Birthday, 
			SearchKeyword, 
			Department, 
			CreateOn, 
			CreateBy, 
			LastChangedOn, 
			LastChangedBy
		)
		values (
			s.IsOrganisation, 
			case when s.IsOrganisation != 0 then null else s.Name1 end, 
			case when s.IsOrganisation != 0 then null else s.Name2 end, 
			case when s.IsOrganisation != 0 then s.Name1 else null end, 
			case when s.IsOrganisation != 0 then s.Name2 else null end,
			s.Sex,
			s.LegalFormKey, 
			s.VatRegNo, 
			s.Notice, 
			s.Birthday, 
			s.SearchKeyword, 
			s.Department,
			s.RecordDateTime,
			s.UserName,
			s.RecordDateTime,
			s.UserName
		)
		when matched then
		update
			set	IsOrganisation = s.IsOrganisation, 
				FirstName = case when s.IsOrganisation != 0 then null else s.Name1 end, 
				LastName = case when s.IsOrganisation != 0 then null else s.Name2 end, 
				CompanyName = case when s.IsOrganisation != 0 then s.Name1 else null end, 
				CompanyNameAdditional = case when s.IsOrganisation != 0 then s.Name2 else null end, 
				Sex = s.Sex,
				LegalFormKey = s.LegalFormKey, 
				VatRegNo = s.VatRegNo, 
				Notice = s.Notice, 
				Birthday = s.Birthday, 
				SearchKeyword = s.SearchKeyword, 
				Department = s.Department, 
				LastChangedOn = s.RecordDateTime, 
				LastChangedBy = s.Username;

	if coalesce(@ContactID, 0) = 0
	begin
		select @ContactID = SCOPE_IDENTITY();
	end

	print '@ContactID: ' + cast(@ContactID as nchar(10))

	--=========================			Ende   =============================================================

	;with titles
	as
	(
		select *
		from (
			select	case when @Title1 is not null then @ContactID else null end 'ContactID', @Title1 'TitleID'
			union all
			select	case when @Title2 is not null then @ContactID else null end 'ContactID', @Title2 'TitleID'
			union all
			select	case when @Title3 is not null then @ContactID else null end 'ContactID', @Title3 'TitleID'
			) myTitle
		where myTitle.ContactID is not null
	)
	merge into dbo.mnContactTitle t
	using titles s on
		s.TitleID = t.TitelID
		and s.ContactID = t.ContactID
	when not matched then
		insert (ContactID, TitelID)
		values (s.ContactID, s.TitleID)
	when not matched by source 
		and t.ContactID = @ContactID
		then
			delete
	;

	if @@ERROR != 0
		goto ExitOnError;

	-- Save Details
	exec @RC = dbo.SaveContactDetails 
		@ContactID = @ContactID,
		@UserID = @UserID,
		@Addresses = @Addresses,
		@ContactOppotunities = @ContactOpportunities,
		@Principals = @Principals


	if @RC != 0 or @@ERROR != 0
		goto ExitOnError;
	
    end try
    begin catch
        set @ErrorMessage = Error_Message();
		goto ExitOnError
    end catch
    
	if @SetTran = 1
	begin
		commit tran
	end
    
    select  @ContactID 'ContactID';
    
    return 0;
    
    ExitOnError:
        if  @SetTran = 1
        begin
            rollback tran
        end
        
        raiserror(@ErrorMessage, 16, 2)
        
        return 1
end
